import pandas as pd
import re
import os
import json


def extract_columns_from_sql(sql):
    """从SQL语句中提取字段列表和表名，支持复杂表名格式"""
    # 移除SQL中的换行符，但保留必要的空格
    sql_clean = sql.strip().replace('\n', ' ')

    # 提取表名（支持包含字母、数字、下划线、冒号、点号和空格的表名）
    table_match = re.search(r'FROM\s+(.*?)\s*[;]?\s*$', sql_clean, re.IGNORECASE | re.DOTALL)
    if not table_match:
        raise ValueError("无法从SQL中提取表名，请检查SQL格式是否包含FROM子句")

    # 清理表名中的多余空格
    table_name = re.sub(r'\s+', ' ', table_match.group(1).strip())

    # 提取SELECT和FROM之间的部分
    match = re.search(r'SELECT (.*?) FROM', sql_clean, re.IGNORECASE | re.DOTALL)
    if not match:
        raise ValueError("无法从SQL中提取字段，请检查SQL格式")

    columns_part = match.group(1)
    # 分割字段，处理可能的别名
    columns = []
    for col in columns_part.split(','):
        col = col.strip()
        # 移除可能的别名
        if ' ' in col:
            col = col.split(' ')[0]
        # 清除字段名中的空格
        col = col.replace(' ', '')
        columns.append(col)

    return columns, table_name


def get_base_table_name(full_table_name):
    """从完整表名中提取基础表名（去掉前缀）"""
    # 分割点号、冒号，取最后一部分作为基础表名
    separators = r'[.:]'
    parts = re.split(separators, full_table_name)
    return parts[-1].strip() if parts else full_table_name.strip()


def load_custom_rules(custom_rules_path=None):
    """加载自定义转换规则"""
    if not custom_rules_path:
        # 检查当前目录是否有自定义规则文件
        if os.path.exists('custom_rules.json'):
            custom_rules_path = 'custom_rules.json'
        else:
            return {}

    try:
        with open(custom_rules_path, 'r', encoding='utf-8') as f:
            rules = json.load(f)
        # 验证规则格式
        if not isinstance(rules, dict):
            raise ValueError("自定义规则必须是JSON对象")
        print(f"成功加载 {len(rules)} 条自定义转换规则")
        return rules
    except Exception as e:
        print(f"加载自定义规则失败: {str(e)}，将忽略自定义规则")
        return {}


def generate_kettle_mapping(old_sql, new_sql, mapping_df, custom_rules=None):
    """生成适合Kettle使用的完整SELECT语句，包含备考信息和自定义规则"""
    # 提取旧表和新表的字段及表名
    old_columns, old_table = extract_columns_from_sql(old_sql)
    new_columns, new_table = extract_columns_from_sql(new_sql)

    # 处理自定义规则
    if custom_rules is None:
        custom_rules = {}

    # 清除映射表中字段的空格
    mapping_df['旧字段'] = mapping_df['旧字段'].astype(str).str.replace(' ', '')
    mapping_df['新字段'] = mapping_df['新字段'].astype(str).str.replace(' ', '')

    # 处理备考列，如果不存在则创建空列
    if '备考' not in mapping_df.columns:
        mapping_df['备考'] = ''
    else:
        mapping_df['备考'] = mapping_df['备考'].astype(str).fillna('')

    # 创建字段映射字典和备考信息字典
    mapping_dict = dict(zip(mapping_df['旧字段'], mapping_df['新字段']))
    notes_dict = {}
    # 构建新字段到备考的映射
    for _, row in mapping_df.iterrows():
        new_field = row['新字段']
        note = row['备考'].strip()
        if new_field and note:
            notes_dict[new_field] = note

    # 分析字段类型
    deleted_fields = []  # 旧表有，新表没有
    added_fields = []  # 新表有，旧表没有

    # 检查旧表有但新表没有的字段
    for old_col in old_columns:
        mapped_new_col = mapping_dict.get(old_col)
        if mapped_new_col not in new_columns and old_col not in new_columns:
            deleted_fields.append(old_col)

    # 检查新表有但旧表没有的字段
    reverse_mapping = {v: k for k, v in mapping_dict.items()}
    for new_col in new_columns:
        mapped_old_col = reverse_mapping.get(new_col)
        if mapped_old_col not in old_columns and new_col not in old_columns and new_col not in custom_rules:
            added_fields.append(new_col)

    # 生成映射的SELECT语句部分
    mapped_select = []
    for new_col in new_columns:
        # 优先检查是否有自定义规则
        if new_col in custom_rules:
            default_value = custom_rules[new_col]
            # 构建注释
            note = notes_dict.get(new_col, "")
            comment_parts = ["自定义规则"]
            if note:
                comment_parts.append(note)
            comment = " -- " + "; ".join(comment_parts) if comment_parts else ""
            mapped_select.append(f"{default_value} AS {new_col}{comment}")
            continue

        # 查找常规映射
        mapped_old_col = reverse_mapping.get(new_col)

        if mapped_old_col and mapped_old_col in old_columns:
            mapped_select.append(f"{mapped_old_col} AS {new_col}")
        elif new_col in old_columns:
            mapped_select.append(f"{new_col} AS {new_col}")
        else:
            # 新增字段
            note = notes_dict.get(new_col, "")
            comment_parts = ["新增字段"]
            if note:
                comment_parts.append(note)
            comment = " -- " + "; ".join(comment_parts)
            mapped_select.append(f"NULL AS {new_col}{comment}")

    # 生成完整的SELECT语句
    try:
        select_clause = ",\n    ".join(mapped_select)
        full_select = f"SELECT\n    {select_clause}\nFROM {old_table};"
    except Exception as e:
        print(f"生成SQL时发生错误: {str(e)}")
        full_select = "SELECT " + ", ".join(mapped_select) + f" FROM {old_table};"

    return {
        'full_select_statement': full_select,
        'deleted_fields': deleted_fields,
        'added_fields': added_fields,
        'custom_mapped_fields': list(custom_rules.keys()),
        'old_table': old_table,
        'new_table': new_table
    }


def main():
    print("===== Kettle数据迁移字段映射生成工具 =====")

    # 获取自定义规则文件路径
    custom_rules_path = input("请输入自定义转换规则JSON文件路径（直接回车则使用默认位置）: ").strip()
    custom_rules = load_custom_rules(custom_rules_path)

    # 获取Excel文件路径
    while True:
        excel_path = input("请输入包含字段映射的Excel文件路径: ").strip()
        if os.path.exists(excel_path):
            break
        print("文件不存在，请重新输入！")

    # 输入新表的SQL（先输入新表SQL以确定要使用的Sheet）
    print("\n请输入新表的SELECT语句（输入空行结束）:")
    new_sql_lines = []
    while True:
        line = input()
        if not line:
            break
        new_sql_lines.append(line)
    new_sql = '\n'.join(new_sql_lines)

    # 提取新表名并确定基础表名
    try:
        _, new_table_full = extract_columns_from_sql(new_sql)
        new_table_base = get_base_table_name(new_table_full)
        print(f"\n识别到新表完整名称: {new_table_full}")
        print(f"提取基础表名（用于匹配Sheet）: {new_table_base}")
    except Exception as e:
        print(f"解析新表信息失败: {str(e)}")
        return

    # 读取Excel中的所有Sheet信息
    try:
        excel_sheets = pd.ExcelFile(excel_path).sheet_names
        print(f"\nExcel文件包含以下Sheet: {', '.join(excel_sheets)}")

        # 查找匹配的Sheet
        if new_table_base in excel_sheets:
            mapping_df = pd.read_excel(excel_path, sheet_name=new_table_base)
            print(f"成功加载匹配的Sheet: {new_table_base}")
        else:
            print(f"错误: 未找到与基础表名 '{new_table_base}' 匹配的Sheet")
            print("请检查Excel文件中的Sheet名称是否与表名匹配")
            return

        # 检查必要的列是否存在
        required_columns = ['旧字段', '新字段']
        if not all(col in mapping_df.columns for col in required_columns):
            print(f"错误: Sheet '{new_table_base}' 必须包含'旧字段'和'新字段'两列！")
            return

        # 检查是否有备考列
        if '备考' in mapping_df.columns:
            print(f"Sheet包含 {len(mapping_df)} 条映射（包含备考信息）")
        else:
            print(f"Sheet包含 {len(mapping_df)} 条映射（未发现备考列）")

    except Exception as e:
        print(f"读取Excel文件失败: {str(e)}")
        return

    # 输入旧表的SQL
    print("\n请输入旧表的SELECT语句（输入空行结束）:")
    old_sql_lines = []
    while True:
        line = input()
        if not line:
            break
        old_sql_lines.append(line)
    old_sql = '\n'.join(old_sql_lines)

    # 生成映射
    try:
        result = generate_kettle_mapping(old_sql, new_sql, mapping_df, custom_rules)

        # 显示结果
        print("\n===== 表信息 =====")
        print(f"旧表: {result['old_table']}")
        print(f"新表: {result['new_table']}")
        print(f"使用的映射Sheet: {new_table_base}")

        print("\n===== 字段类型分析 =====")
        print(f"1. 自定义映射字段: {len(result['custom_mapped_fields'])} 个")
        for field in result['custom_mapped_fields']:
            print(f"   - {field}")

        print(f"\n2. 删除字段: {len(result['deleted_fields'])} 个")
        for field in result['deleted_fields']:
            print(f"   - {field}")

        print(f"\n3. 新增字段: {len(result['added_fields'])} 个")
        for field in result['added_fields']:
            print(f"   - {field}")

        print("\n===== 生成的完整SELECT语句 =====")
        print(result['full_select_statement'])

        # 保存到文件
        save_path = f"kettle_mapping_{new_table_base}.sql"
        with open(save_path, 'w', encoding='utf-8') as f:
            f.write(f"-- 目标表: {result['new_table']}\n")
            f.write(f"-- 映射来源Sheet: {new_table_base}\n")
            f.write(result['full_select_statement'])
        print(f"\n映射结果已保存到 {os.path.abspath(save_path)}")

    except Exception as e:
        print(f"生成映射失败: {str(e)}")


if __name__ == "__main__":
    main()
